import pymysql
import xlrd, os
import requests
import json

def get_tel(n):
    data = xlrd.open_workbook(r'/Users/jumei/Desktop/推荐车辆明细.xls')
    table = data.sheet_by_index(0)
    try:
        cell_tel = str(int(table.cell(n, 7).value))  # 第3行 8列
    except Exception as e:
        cell_tel = ''
    return cell_tel


class ExecSQL:
    conf = {
        'host': '192.168.16.18',
        'port': 6006,
        'user': 'dev',
        'password': 'jmdevcd',
        'db': 'growth_unmanned_shelves',
        'charset': 'utf8'
    }

    def get_count_red_envelop(self, tel):
        sql = 'select a.uid,b.mobile,count(*) from red_envelope a inner join meidian_driver b on a.uid = b.uid where a.uid = (select uid from  meidian_driver where mobile = {})'.format(
            tel)
        conn = pymysql.connect(**self.conf)
        cur = conn.cursor()
        cur.execute(sql)
        msg = cur.fetchone()
        conn.commit()
        # 关闭游标
        cur.close()
        return msg

    def get_conut_newguest(self, tel):
        sql = 'select a.uid,b.mobile,count(*) from red_envelope a inner join meidian_driver b on a.uid = b.uid where a.uid = (select uid from  meidian_driver a where mobile = {})'.format(
            tel)
        conn = pymysql.connect(**self.conf)
        cur = conn.cursor()
        cur.execute(sql)
        msg = cur.fetchone()
        conn.commit()
        # 关闭游标
        cur.close()
        return msg



def crm_api_getuid(tel):
    headers = {
        'Cookie': 'MDSID=l2bnb8ne4j9oh3roc401p4kob3; SERVERID=18'
    }
    url = 'http://crm.hhbg123.com/Backstage/Driver/DriverList'
    params = {
        'pageSize': '20',
        'pageIndex': '1',
        'uids':'',
        'mobiles':tel
    }
    get_uid = json.loads(requests.get(url=url,params=params,headers=headers).text)['data']['list'][0]['uid']
    return get_uid

def crm_api_cnt_red(uid):
    headers = {
        'Cookie': 'MDSID=l2bnb8ne4j9oh3roc401p4kob3; SERVERID=18'
    }
    url = 'http://crm.hhbg123.com/Backstage/RedPackageRecord/getList'

    params ='pageSize=10&pageIndex=1&activity_name=&bonus_type=&time%5B%5D=2018-05-31T16%3A00%3A00.000Z&time%5B%5D=2018-06-30T16%3A00%3A00.000Z&uid={}'.format(uid)
    print(params)
    get_cnt =json.loads(requests.get(url=url,params=params,headers=headers).text)['data']['count']
    return get_cnt




if __name__ == '__main__':
    with open('compare_red.csv', 'w') as f:
        mobiles = (get_tel(n) for n in range(2, 200))
        for mobile in mobiles:
            if mobile!= '':
                try :
                    uid = str(crm_api_getuid(mobile))
                    if int(uid) > 0:
                        cnt = str(crm_api_cnt_red(uid))
                        msg = mobile + "," + cnt + '\n'
                    else :
                        msg = mobile + "," + str(0) + '\n'
                    f.write(msg)
                    print(msg)
                except :
                    pass

